﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using SQLite.NET;


namespace BierdopjeTV.Database
{

    class DBSubs
    {

        public static int getShowSubs(int ShowID, string taal)
        {
            int aantal = 0;
            System.Diagnostics.Debug.Print("getShowSubs(" + ShowID.ToString() +  " , " + taal + ")");
            string strFileName = "";
            string strSuffix = "";
            
            string _Query;

            _Query = "";
            _Query += "SELECT" + Environment.NewLine;
            _Query += "	BierdopjeEpisodes.EpisodeID," + Environment.NewLine;
            _Query += "	BierdopjeEpisodes.Season," + Environment.NewLine;
            _Query += "	BierdopjeEpisodes.Episode," + Environment.NewLine;
            _Query += "	Episodes.Codec," + Environment.NewLine;
            _Query += "	Episodes.Source," + Environment.NewLine;
            _Query += "	Episodes.Path" + Environment.NewLine;
            _Query += "FROM" + Environment.NewLine;
            _Query += "	BierdopjeEpisodes" + Environment.NewLine;
            _Query += "LEFT JOIN" + Environment.NewLine;
            _Query += "	Episodes" + Environment.NewLine;
            _Query += "	ON" + Environment.NewLine;
            _Query += "	(" + Environment.NewLine;
            _Query += "		Episodes.ShowID = BierdopjeEpisodes.ShowID" + Environment.NewLine;
            _Query += "		AND" + Environment.NewLine;
            _Query += "		Episodes.Season = BierdopjeEpisodes.Season" + Environment.NewLine;
            _Query += "		AND" + Environment.NewLine;
            _Query += "		Episodes.Episode = BierdopjeEpisodes.Episode" + Environment.NewLine;
            _Query += "	)" + Environment.NewLine;
            _Query += "WHERE" + Environment.NewLine;
            _Query += "	BierdopjeEpisodes.ShowID = " + ShowID.ToString() + Environment.NewLine;
            _Query += "	AND" + Environment.NewLine;
            _Query += "	Downloaded = 1" + Environment.NewLine;
            _Query += "	AND" + Environment.NewLine;
            switch (taal)
            {
                case "nl":
                    _Query += "	SubNLDownloaded = 0" + Environment.NewLine;
                    strSuffix = Database.getSettingValue("AchtervoegselNLSub");
                    break;
                case "en":
                    _Query += "	SubENDownloaded = 0" + Environment.NewLine;
                    strSuffix = Database.getSettingValue("AchtervoegselENSub");
                    break;
            }
            SQLiteResultSet _Results = Helpers.SqliteHelper.ExecuteQuery(_Query);
            Helpers.Logger.Write(_Results.Rows.Count.ToString() + " sub(s) gevonden!!!", "getShowSubs()");
            if (_Results.Rows.Count > 0)
            {
                for (int i = 0; i < _Results.Rows.Count; i++)
                {
                    API.APIFunctions _API = new API.APIFunctions();

                    try
                    {
                        List<API.BierdopjeSubs> _Subs = _API.getSubs(Convert.ToInt32(_Results.Rows[i].fields[0]), taal);
                        if (_Subs.Count > 0)
                        {
                            //loop de subs na, en zoek dezelfde codec + source
                            for (int j = 0; j < _Subs.Count; j++)
                            {
                                if (_Subs[j].Source.ToUpper() == _Results.Rows[i].fields[4].ToUpper())
                                {
                                    if (_Subs[j].Codec.ToUpper() == _Results.Rows[i].fields[3].ToUpper())
                                    {
                                        System.Diagnostics.Debug.Print("Sub gevonden!!! -> " + _Subs[j].FileName);
                                        System.Diagnostics.Debug.Print("\t " + _Subs[j].DownloadLink);
                                        System.Diagnostics.Debug.Print("\t " + _Results.Rows[i].fields[5]);
                                        
                                        strFileName = _Results.Rows[i].fields[5];
                                        strFileName = strFileName.Substring(0, strFileName.LastIndexOf("."));
                                        //was er al een sub gevonden?
                                        if (aantal == 0)
                                        {
                                            strFileName += "." + strSuffix + ".srt";
                                        }
                                        else
                                        {
                                            strFileName += "." + strSuffix + aantal.ToString() + ".srt";
                                        }

                                        Helpers.Logger.Write(strFileName, "DBSubs.getShowSubs()");
                                        Helpers.HTTPHelper.DownloadFile(_Subs[j].DownloadLink, strFileName);

                                        if (System.IO.File.Exists(strFileName))
                                        {
                                            switch (taal)
                                            {
                                                case "nl":
                                                    Helpers.SqliteHelper.ExecuteQuery("UPDATE BierdopjeEpisodes SET SubNLDownloaded = 1 WHERE EpisodeID = " + _Subs[j].EpisodeID.ToString());
                                                    break;
                                                case "en":
                                                    Helpers.SqliteHelper.ExecuteQuery("UPDATE BierdopjeEpisodes SET SubENDownloaded = 1 WHERE EpisodeID = " + _Subs[j].EpisodeID.ToString());
                                                    break;
                                            } 
                                        }

                                        //zeg dat we wat gevonden hebben :-)
                                        aantal++;
                                    }
                                }
                            }

                            
                        }
                    }
                    catch (Exception ex)
                    {
                        System.Diagnostics.Debug.Print(ex.Message);
                    }
                    
                }
            }
            else
            {
                Helpers.Logger.Write("Geen local episodes gevonden voor Show" + ShowID.ToString() + ", taal : " + taal, "DBSubs.getShowSubs()");
            }

            return aantal;


        }

        public static int getSeasonSubs(int ShowID, int Season, string taal)
        {
            int aantal = 0;

            System.Diagnostics.Debug.Print("getSeasonSubs(" + ShowID.ToString() + " , " + Season.ToString() + " , " + taal + ")");
            string strFileName = "";
            string strSuffix = "";

            string _Query;
            
            _Query = "";
            _Query += "SELECT" + Environment.NewLine;
            _Query += "	BierdopjeEpisodes.EpisodeID," + Environment.NewLine;
            _Query += "	BierdopjeEpisodes.Season," + Environment.NewLine;
            _Query += "	BierdopjeEpisodes.Episode," + Environment.NewLine;            
            _Query += "	Episodes.Codec," + Environment.NewLine;
            _Query += "	Episodes.Source," + Environment.NewLine;
            _Query += "	Episodes.Path" + Environment.NewLine;
            _Query += "FROM" + Environment.NewLine;
            _Query += "	BierdopjeEpisodes" + Environment.NewLine;
            _Query += "LEFT JOIN" + Environment.NewLine;
            _Query += "	Episodes" + Environment.NewLine;
            _Query += "	ON" + Environment.NewLine;
            _Query += "	(" + Environment.NewLine;
            _Query += "		Episodes.ShowID = BierdopjeEpisodes.ShowID" + Environment.NewLine;
            _Query += "		AND" + Environment.NewLine;
            _Query += "		Episodes.Season = BierdopjeEpisodes.Season" + Environment.NewLine;
            _Query += "		AND" + Environment.NewLine;
            _Query += "		Episodes.Episode = BierdopjeEpisodes.Episode" + Environment.NewLine;
            _Query += "	)" + Environment.NewLine;
            _Query += "WHERE" + Environment.NewLine;
            _Query += "	BierdopjeEpisodes.ShowID = " + ShowID + Environment.NewLine;
            _Query += "	AND" + Environment.NewLine;
            _Query += "	BierdopjeEpisodes.Season = " + Season + Environment.NewLine;
            _Query += "	AND" + Environment.NewLine;
            _Query += "	Downloaded = 1" + Environment.NewLine; 
            _Query += "	AND" + Environment.NewLine;
           
            switch (taal)
            {
                case "nl":
                    _Query += "	SubNLDownloaded = 0" + Environment.NewLine;
                    strSuffix = Database.getSettingValue("AchtervoegselNLSub");
                    break;
                case "en":
                    _Query += "	SubENDownloaded = 0" + Environment.NewLine;
                    strSuffix = Database.getSettingValue("AchtervoegselENSub");
                    break;
            }            
            SQLiteResultSet _Results = Helpers.SqliteHelper.ExecuteQuery(_Query);
            Helpers.Logger.Write(_Results.Rows.Count.ToString() + " sub(s) gevonden!!!", "getSeasonSubs()");
            if (_Results.Rows.Count > 0)
            {
                //_API.getSubs(433927, "nl");


                for (int i = 0; i < _Results.Rows.Count; i++)
                {
                    API.APIFunctions _API = new API.APIFunctions();

                    try
                    {
                        List<API.BierdopjeSubs> _Subs = _API.getSubs(Convert.ToInt32(_Results.Rows[i].fields[0]), taal);
                        if (_Subs.Count > 0)
                        {
                            //loop de subs na, en zoek dezelfde codec + source
                            for (int j = 0; j < _Subs.Count; j++)
                            {
                                if (_Subs[j].Source.ToUpper() == _Results.Rows[i].fields[4].ToUpper())
                                {
                                    if (_Subs[j].Codec.ToUpper() == _Results.Rows[i].fields[3].ToUpper())
                                    {
                                        strFileName = _Results.Rows[i].fields[5];
                                        strFileName = strFileName.Substring(0, strFileName.LastIndexOf("."));
                                        //was er al een sub gevonden?
                                        if (aantal == 0)
                                        {
                                            strFileName += "." + strSuffix + ".srt";
                                        }
                                        else
                                        {
                                            strFileName += "." + strSuffix + aantal.ToString() + ".srt";
                                        }
                                        
                                        Helpers.Logger.Write(strFileName, "DBSubs.getSeasonSubs()");
                                        Helpers.HTTPHelper.DownloadFile(_Subs[j].DownloadLink, strFileName);


                                        if (System.IO.File.Exists(strFileName))
                                        {
                                            switch (taal)
                                            {
                                                case "nl":
                                                    Helpers.SqliteHelper.ExecuteQuery("UPDATE BierdopjeEpisodes SET SubNLDownloaded = 1 WHERE EpisodeID = " + _Subs[j].EpisodeID.ToString());
                                                    break;
                                                case "en":
                                                    Helpers.SqliteHelper.ExecuteQuery("UPDATE BierdopjeEpisodes SET SubENDownloaded = 1 WHERE EpisodeID = " + _Subs[j].EpisodeID.ToString());
                                                    break;
                                            }
                                        }

                                        //zeg dat we wat gevonden hebben :-)
                                        aantal++;
                                        
                                    }
                                }
                            }


                        }
                    }
                    catch (Exception ex)
                    {
                        System.Diagnostics.Debug.Print(ex.Message);
                    }

                }
            }
            else
            {
                Helpers.Logger.Write("Geen local episodes gevonden voor Season " + ShowID.ToString() + ", Season, " + Season.ToString() + " taal : " + taal, "DBSubs.getSeasonSubs()");
            }

            return aantal;
        }

        public static int getEpisodeSubs(int EpisodeID, string taal)
        {
            Helpers.Logger.Write("getEpisodeSubs(" + EpisodeID.ToString() + " , " + taal + ")", "GetEpisodeSubs()");
            string strFileName = "";
            string strSuffix = "";
            int aantal = 0;

            string _Query;

            _Query = "";
            _Query += "SELECT" + Environment.NewLine;
            _Query += "	BierdopjeEpisodes.EpisodeID," + Environment.NewLine;
            _Query += "	BierdopjeEpisodes.Season," + Environment.NewLine;
            _Query += "	BierdopjeEpisodes.Episode," + Environment.NewLine;
            _Query += "	Episodes.Codec," + Environment.NewLine;
            _Query += "	Episodes.Source," + Environment.NewLine;
            _Query += "	Episodes.Path" + Environment.NewLine;
            _Query += "FROM" + Environment.NewLine;
            _Query += "	BierdopjeEpisodes" + Environment.NewLine;
            _Query += "LEFT JOIN" + Environment.NewLine;
            _Query += "	Episodes" + Environment.NewLine;
            _Query += "	ON" + Environment.NewLine;
            _Query += "	(" + Environment.NewLine;
            _Query += "		Episodes.ShowID = BierdopjeEpisodes.ShowID" + Environment.NewLine;
            _Query += "		AND" + Environment.NewLine;
            _Query += "		Episodes.Season = BierdopjeEpisodes.Season" + Environment.NewLine;
            _Query += "		AND" + Environment.NewLine;
            _Query += "		Episodes.Episode = BierdopjeEpisodes.Episode" + Environment.NewLine;
            _Query += "	)" + Environment.NewLine;
            _Query += "WHERE" + Environment.NewLine;
            _Query += "	BierdopjeEpisodes.EpisodeID = " + EpisodeID + Environment.NewLine;
            _Query += "	AND" + Environment.NewLine;
            switch (taal)
            {
                case "nl":
                    _Query += "	SubNLDownloaded = 0" + Environment.NewLine;
                    strSuffix = Database.getSettingValue("AchtervoegselNLSub");
                    break;
                case "en":
                    _Query += "	SubENDownloaded = 0" + Environment.NewLine;
                    strSuffix = Database.getSettingValue("AchtervoegselENSub");
                    break;
            }            
            SQLiteResultSet _Results = Helpers.SqliteHelper.ExecuteQuery(_Query);            
            if (_Results.Rows.Count > 0)
            {
                for (int i = 0; i < _Results.Rows.Count; i++)
                {
                    API.APIFunctions _API = new API.APIFunctions();

                    try
                    {
                        List<API.BierdopjeSubs> _Subs = _API.getSubs(Convert.ToInt32(_Results.Rows[i].fields[0]), taal);
                        if (_Subs.Count > 0)
                        {
                            //loop de subs na, en zoek dezelfde codec + source
                            for (int j = 0; j < _Subs.Count; j++)
                            {
                                //Source (HDTV) vs Bierdopje source (HDTV?)
                                if (_Subs[j].Source.ToUpper() == _Results.Rows[i].fields[4].ToUpper())
                                {
                                    //Codec (x264) vs Bierdopje Codec (x264?)
                                    if (_Subs[j].Codec.ToUpper() == _Results.Rows[i].fields[3].ToUpper())
                                    {                                                                              
                                        strFileName = _Results.Rows[i].fields[5];
                                        strFileName = strFileName.Substring(0, strFileName.LastIndexOf("."));
                                        
                                        //was er al een sub gevonden?
                                        if (aantal == 0)
                                        {
                                            strFileName += "." + strSuffix + ".srt";
                                        }
                                        else
                                        {
                                            strFileName += "." + strSuffix + aantal.ToString() + ".srt";
                                        }

                                        Helpers.Logger.Write("Downloading " + strFileName, "DBSubs.getEpisodeSubs()");
                                        //download hem
                                        Helpers.HTTPHelper.DownloadFile(_Subs[j].DownloadLink, strFileName);

                                        //is de file gedownload?
                                        if (System.IO.File.Exists(strFileName))
                                        {
                                            switch (taal)
                                            {
                                                case "nl":
                                                    Helpers.SqliteHelper.ExecuteQuery("UPDATE BierdopjeEpisodes SET SubNLDownloaded = 1 WHERE EpisodeID = " + EpisodeID.ToString());
                                                    break;
                                                case "en":
                                                    Helpers.SqliteHelper.ExecuteQuery("UPDATE BierdopjeEpisodes SET SubENDownloaded = 1 WHERE EpisodeID = " + EpisodeID.ToString());
                                                    break;
                                            }
                                        }
                                        //zeg dat we wat gevonden hebben :-)
                                        aantal++;
                                        
                                    }
                                }
                            }


                        }
                    }
                    catch (Exception ex)
                    {
                        System.Diagnostics.Debug.Print(ex.Message);
                    }

                }
            }
            else
            {
                Helpers.Logger.Write("Geen local episodes gevonden voor " + EpisodeID.ToString() + ", taal : " + taal, "DBSubs.getEpisodeSubs()");
            }

            return aantal;
        }
        
    }
}
